﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.ComponentModel;

namespace Mooc
{
    public class SqlDao
    {
        static string url = @"server=localhost;Allow User Variables=True;database=mooc; uid=root;pwd=123456;Character Set=utf8;";

        public MySqlConnection getConnection()
        {
            MySqlConnection conn;
            try
            {
                conn = new MySqlConnection(url);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        MySqlDataAdapter adapter;

        MySqlDataReader reader;
        DataSet ds;

        MySqlConnection conn;

        MySqlCommand comm;

        string sql;

        public Stu stuLogin(string id, string pass)
        {
            Stu stu = new Stu();
            stu.S_id = "";
            try
            {
                conn = getConnection();
                sql = "select s_id, s_pass, s_name from student where s_id=@id and s_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    stu.S_id = reader["s_id"].ToString();
                    stu.S_name = reader["s_name"].ToString();
                    stu.S_pass = reader["s_pass"].ToString();
                }
                return stu;
            }
            catch (Exception ex)
            {
                return stu;
            }
            finally
            {
                conn.Close();
            }
        }

        public Tea teaLogin(string id, string pass)
        {
            Tea teacher = new Tea();
            teacher.T_id = "";
            try
            {
                conn = getConnection();
                sql = "select t_id, t_pass, t_name from teacher where t_id=@id and t_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    teacher.T_id = reader["t_id"].ToString();
                    teacher.T_name = reader["t_name"].ToString();
                    teacher.T_pass = reader["t_pass"].ToString();
                }
                return teacher;
            }
            catch (Exception ex)
            {
                return teacher;
            }
            finally
            {
                conn.Close();
            }
        }

        public Edu issLogin(string id, string pass)
        {
            Edu edu = new Edu();
            edu.E_id = "";
            try
            {
                conn = getConnection();
                sql = "select e_id, e_pass, e_name from educational where e_id=@id and e_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    edu.E_id = reader["e_id"].ToString();
                    edu.E_name = reader["e_name"].ToString();
                    edu.E_pass = reader["e_pass"].ToString();
                }
                return edu;
            }
            catch (Exception ex)
            {
                return edu;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertWork(Work work)
        {
            try
            {
                conn = getConnection();
                sql = "insert into work(w_id,w_content,w_time,s_id)" +
                    "values(@w_id,@w_content,@w_time,@s_id)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("w_id", work.W_id);
                comm.Parameters.AddWithValue("w_content", work.W_content);
                comm.Parameters.AddWithValue("w_time", work.W_time);
                comm.Parameters.AddWithValue("s_id", work.S_id);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertOpinion(Opi opi)
        {
            try
            {
                conn = getConnection();
                sql = "insert into opinion(o_id,w_id,o_comment,o_time,e_id)" +
                    "values(@o_id,@w_id,@o_comment,@o_time,@e_id)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("o_id", opi.O_id);
                comm.Parameters.AddWithValue("w_id", opi.W_id);
                comm.Parameters.AddWithValue("o_comment", opi.O_comment);
                comm.Parameters.AddWithValue("o_time", opi.O_time);
                comm.Parameters.AddWithValue("e_id", opi.E_id);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectWorkById(string id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT w_id 作业ID, t_name 教师, w_content 作业内容,w_score 分数," +
                    "w_comment 评语 FROM `work` JOIN teacher USING(t_id) where s_id=@id and w_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectWorkedById(string id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT w_id 作业ID, w_content 作业内容, w_time 上传时间 FROM `work` where s_id=@id and w_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectWorkByState(int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT w_id 作业ID, s_id 学号, s_name 学生, w_content 作业内容 " +
                    " FROM `work` JOIN student USING(s_id) where w_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectWorkedByState(int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT t_name 老师,w_id 作业ID, w_content 作业内容,w_score 分数, w_comment 评语" +
                    " FROM `work` JOIN teacher USING(t_id) where w_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectOpinionById(string t_id)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT e_name 抽检人, o_time 抽检时间, w_content 作业内容, w_score 分数, w_comment 评语, " +
                    "o_comment 意见 FROM opinion JOIN `work` USING(w_id) JOIN educational USING(e_id) WHERE t_id=@id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", t_id);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateWork(string w_id, string s_id, string content)
        {
            try
            { 
                conn = getConnection();
                sql = "update work set w_content=@content " +
                    "where w_id=@w_id and s_id=@s_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("w_id", w_id);
                comm.Parameters.AddWithValue("s_id", s_id);
                comm.Parameters.AddWithValue("content", content);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateWork(Work work)
        {
            try
            {
                conn = getConnection();
                sql = "update work set t_id=@t_id,w_score=@score,w_comment=@comment," +
                    "w_state=@state where w_id=@w_id and s_id=@s_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("t_id", work.T_id);
                comm.Parameters.AddWithValue("score", work.W_score);
                comm.Parameters.AddWithValue("comment", work.W_comment);
                comm.Parameters.AddWithValue("w_id", work.W_id);
                comm.Parameters.AddWithValue("s_id", work.S_id);
                comm.Parameters.AddWithValue("state", work.W_state);
                return comm.ExecuteNonQuery()>0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
